package busmap.databaseAPI;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataHelper {
	 
	   private static final String DATABASE_NAME = "busmap.db";
	   private static final int DATABASE_VERSION = 1;
	   private static final String STREETS_TABLE = "streets";
	   private static final String BUS_STOP = "busstop";
	   private static final String BUS_ROUTE = "busroute";
	   private static final String BUS_GO = "busgo";
	 
	   private Context context;
	   private SQLiteDatabase db;
	 

	   public DataHelper(Context context) {
	      this.context = context;
	      OpenHelper openHelper = new OpenHelper(this.context);
	      this.db = openHelper.getWritableDatabase();
	    }
	 
	
		public void deleteAll() {
		      this.db.delete(STREETS_TABLE, null, null);
		      this.db.delete(BUS_STOP, null, null);
		      this.db.delete(BUS_ROUTE, null, null);
		      this.db.delete(BUS_GO, null, null);
		      
		   } 
		
		public void dropAll(){
				db.execSQL("DROP TABLE IF EXISTS " + STREETS_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + BUS_STOP);
				db.execSQL("DROP TABLE IF EXISTS " + BUS_ROUTE);
				db.execSQL("DROP TABLE IF EXISTS " + BUS_GO);
		}
		
		public void insertStreet(String street){
		if(!checkStreet(street)){
			db.rawQuery("INSERT INTO " + STREETS_TABLE + "(name) VALUES( '" + street + "' ) ",null );
			}			
		}
		
		
		public void insertBusStop(int AddressNo, int DistrictNo,int  StreetNo){
			if(!checkBusStop(AddressNo, DistrictNo, StreetNo)){
				db.rawQuery("INSERT INTO " + BUS_STOP + "(AddressNo, DistrictNo, StreetNo ) VALUES( "+ AddressNo + " , " + DistrictNo   + " , " +  StreetNo +   " ) ",null );
			}
			
		}		
		
		public void insertBusRoute(int id, String name){
			ContentValues initialValues = new ContentValues(); 
			initialValues.put("id", id);
			initialValues.put("name", name);
			db.insert(BUS_ROUTE, null, initialValues);
		}
		
		
		public void insertBusGo(int busstopNo, int busrouteno, int seqNo){
			ContentValues initialValues = new ContentValues(); 
			initialValues.put("busstopNo", busstopNo);
			initialValues.put("busrouteno", busrouteno);
			initialValues.put("seqNo", seqNo);
			db.insert(BUS_GO, null, initialValues);
			
		}
		
		public void deleteStreet(int id){
			db.delete(STREETS_TABLE, "id = ?", new String[] {Integer.toString(id)});
		}
		
		public void deleteBusStop(int id){
			db.delete(BUS_STOP, "id = ?", new String[] {Integer.toString(id)});
		}
		
		
		public void deleteBusRoute(int id){
			db.delete(BUS_ROUTE, "id = ?", new String[] {Integer.toString(id)});			
		}
		
		public void deleteBusGo(int busstopNo, int busrouteno){
			db.delete(BUS_GO, "busstopNo = ? and busrouteno = ?", new String[] {Integer.toString(busstopNo), Integer.toString(busrouteno)});
		}
		
		
		public int getStreetID(String street){
			Cursor cursor = db.query(STREETS_TABLE, new String[] {"id"}, "street = ?", new String[] {street}, null, null, null);
			if(cursor.moveToFirst()){
				return cursor.getInt(1);
			}
			return -1;
		}
		
		public int getBusStopID(int AddressNo, int DistrictNo,int  StreetNo ){
			Cursor cursor =  db.query(BUS_STOP, new String [] {"id"}, "AddressNo = ? AND DistrictNo = ? AND StreetNo = ? ", new String [] { Integer.toString(AddressNo), Integer.toString(DistrictNo), Integer.toString(StreetNo)}, null, null, null) ;
			if(cursor.moveToFirst()){
				return cursor.getInt(1);
			}
			return -1;
		}
		
	   private boolean checkBusStop(int AddressNo, int DistrictNo,int  StreetNo ){
		   Cursor cursor = db.query(BUS_STOP, new String [] {"id"} , " AddressNo = ?, DistrictNo = ?, StreetNo = ? ", new String [] { Integer.toString(AddressNo), Integer.toString(DistrictNo) ,Integer.toString( StreetNo)},  null, null, null);
			if(cursor.moveToFirst())
				return true;
			return false;
	   }
		
		private boolean checkStreet(String street){
			Cursor cursor = db.query(STREETS_TABLE, new String [] {"id"} , " name = ? ", new String [] {street},  null, null, null);
			if(cursor.moveToFirst())
				return true;
			return false;
		}
		
		
		

	 
	   private static class OpenHelper extends SQLiteOpenHelper {
	 
	      OpenHelper(Context context) {
	         super(context, DATABASE_NAME, null, DATABASE_VERSION);
	      }
	 
	      @Override
	      public void onCreate(SQLiteDatabase db) {
	         db.execSQL("CREATE TABLE IF NOT EXISTS " + STREETS_TABLE + "(id INTEGER  PRIMARY KEY  AUTOINCREMENT, name TEXT)");
	         db.execSQL("CREATE TABLE IF NOT EXISTS " + BUS_STOP + 
	        		 "(id INTEGER PRIMARY KEY AUTOINCREMENT ," +
	        		 "AddressNo INTEGER," +
	        		 "DistrictNo INTEGER," + 
	        		 "StreetNo INTEGER, " +
	        		 "UNIQUE(AddressNo, DistrictNo, StreetNo), "+ 
	        		 "FOREIGN KEY(StreetNo) REFERENCES " + STREETS_TABLE + " (id) )");
	         
	         db.execSQL("CREATE TABLE IF NOT EXISTS " + BUS_ROUTE + 
	        		 "(id INTEGER PRIMARY KEY,"+
	        		 "name TEXT)" );
	         
	         
	         db.execSQL("CREATE TABLE IF NOT EXISTS " + BUS_GO + 
	        		 "(busstopNo INTEGER, "+
	        		 "busrouteNo INTEGER, " + 
	        		 "seqNo INTEGER NOT NULL, " +
	        		 "PRIMARY KEY(busstopNo, busrouteNo) ," + 
	        		 "FOREIGN KEY (busstopNo) REFERENCES "+ BUS_STOP + " (id), " +
	        		 "FOREIGN KEY (busrouteNo) REFERENCES "+ BUS_ROUTE + " (id) )" );
	        }
	      
	      
	 
	      @Override
	      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
	         db.execSQL("DROP TABLE IF EXISTS " + STREETS_TABLE);
	         db.execSQL("DROP TABLE IF EXISTS " + BUS_STOP);
	         db.execSQL("DROP TABLE IF EXISTS " + BUS_ROUTE);
	         db.execSQL("DROP TABLE IF EXISTS " + BUS_GO);
	         onCreate(db);
	      } 
	   }
	}